﻿using System;
using System.Runtime.Serialization;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Windows.Forms;
using System.IO;
namespace MailMerge_Generator
{
    class Utilities
    {
        public static object GetConnectionString(String file, Boolean includeExcel)
        {
            String sExtension = file.Substring(file.LastIndexOf(".") + 1);
            object oConn = null;
            //Either I need to figure out why the client machine does not like Microsoft.ACE.OLEDB.12.0 or
            //I will need to change this code to work without it?
            switch (sExtension)
            {
                case "xlsx":
                    if (includeExcel)
	                {
                        oConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + (Char)34 + file + (Char)34 + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";";
	                }
                    else
                    {
                        oConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + (Char)34 + file + (Char)34 + ";Extended Properties=\"HDR=YES;IMEX=1\";";
                    }
                        break;
                case "xls":
                    if (includeExcel)
                    {
                        oConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + (Char)34 + file + (Char)34 + ";Extended Properties=\"Excel 8.0;HDR=YES;\";";
                    }
                    else
                    {
                        oConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + (Char)34 + file + (Char)34 + ";Extended Properties=\"HDR=YES;\";";
                    }
                    break;
                default:
                    throw new TypeLoadException();
            }
            return oConn;
        }
        public static String FindFile(String output)
        {
            OpenFileDialog fdlg = new OpenFileDialog();
            fdlg.Title = "Select " + output;
            //fdlg.InitialDirectory = "c:\\";
            fdlg.Filter = "All files (*.*)|*.*|Excel Spreadsheets (*.xlsx) (*.xlsx)|*.xlsx;*.xls";
            fdlg.FilterIndex = 2;
            fdlg.RestoreDirectory = true;
            fdlg.Multiselect = false;
            if (fdlg.ShowDialog() == DialogResult.OK)
            {
                return fdlg.FileName;
            }
            else
            {
                return "";
            }
        }
        public static String FindSheetName(String file)
        {
            OleDbConnection objConn = null;
            System.Data.DataTable dt = null;
            try
            {
                String sheetName;
                FileInfo fileInfo = new FileInfo(file);
                String connString = (String)GetConnectionString(file, true);
                objConn = new OleDbConnection(connString);
                objConn.Open();
                dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (dt == null)
                {
                    return null;
                }
                DataRow row = dt.Rows[0];
                sheetName = row["TABLE_NAME"].ToString();
                return sheetName;
            }
            catch (Exception e)
            {
                if (!File.Exists(file))
                {
                    return null;
                }
                else
                {
                    MessageBox.Show("An unexpected error occurred:" + (Char)10 + e);
                    return null;
                }
            }
        }
    }
    public class Exception<T> : Exception
    {
        public Exception()
            : base()
        {
        }

        public Exception(string message)
            : base(message)
        {
        }

        public Exception(string message, Exception inner)
            : base(message, inner)
        {
        }

        public Exception(SerializationInfo info, StreamingContext context)
            : base(info, context)
        {
        }
    }

}
